﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace elctronixMVC.Models
{
    public class urunler
    {
        electronicDataDataContext db = new electronicDataDataContext();
        public int productID { get; set; }
        public string productName { get; set; }
        public string model { get; set; }
        public double price { get; set; }
        public int categoryID { get; set; }
        public int supplierID { get; set; }  //marka ID
        public string brand { get; set; }  //marka Adı
        public long stock { get; set; }
        public int unitsonorder { get; set; }
        public int reorderlevel { get; set; }  //kritik stok seviyesi
        public double discount { get; set; }
        public int status { get; set; }
        public string adddate { get; set; }
        
        public string status_adi { get; set; }
        public string categoryname { get; set; }
        public string photopath { get; set; }
        public int resimID { get; set; }
        public string detaylisorgu { get; set; }






        public static List<urunler> urunlergetircatIDli(int cid)
        {
            List<urunler> prd = new List<urunler>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p inner join categories as c on p.categoryID=c.categoryID  inner join resimler as r on p.productID=r.productID  where p.categoryID=@cid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@cid", cid);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                urunler p = new urunler();
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                p.categoryname = sdr["categoryname"].ToString();
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                p.photopath = sdr["photopath"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }  // p.urunlergetircatIDli();  static olmadıgı icin , nesne yaratılarak cagrılır ..Değişen veri döndürdügü icin static yapmadık
           // birden fazla satır dönder




        public void urungetirIDli()
        {
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p left join resimler as r on p.productID=r.productID where p.productID=@pid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@pid", productID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                productName = sdr["productname"].ToString();
                model = sdr["model"].ToString();
                price = Convert.ToDouble(sdr["price"]);
                categoryID = Convert.ToInt32(sdr["categoryID"]);
                supplierID = Convert.ToInt32(sdr["supplierID"]);
                brand = sdr["brand"].ToString();
                stock = Convert.ToUInt32(sdr["stock"]);
                unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                discount = Convert.ToDouble(sdr["discount"]);
                status = Convert.ToInt32(sdr["status"]);
                photopath = sdr["photopath"].ToString();
                adddate = sdr["adddate"].ToString();
            }
            sqlcon.Close();
        }  // Bu metodu kullanmadik.yukarida reference 0 yazıyor.


        public urunler urungetirIDLIList()
        {
            urunler u = new urunler();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p left join resimler as r on p.productID=r.productID where p.productID=@pid", sqlcon);
            sqlcmd.Parameters.AddWithValue("@pid", productID);
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                u.productID = Convert.ToInt32(sdr["productID"]);
                u.productName = sdr["productname"].ToString();
                u.model = sdr["model"].ToString();
                u.price = Convert.ToDouble(sdr["price"]);
                u.categoryID = Convert.ToInt32(sdr["categoryID"]);
                u.supplierID = Convert.ToInt32(sdr["supplierID"]);
                u.brand = sdr["brand"].ToString();
                u.stock = Convert.ToUInt32(sdr["stock"]);
                u.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                u.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                u.discount = Convert.ToDouble(sdr["discount"]);
                u.status = Convert.ToInt32(sdr["status"]);
                u.photopath = sdr["photopath"].ToString();
                u.adddate = sdr["adddate"].ToString();
            }
            sqlcon.Close();
            return u;
        }  // Tek deger döndürdügü icin List kullanmadık






        public static List<urunler> urunlergetir()
        {
            List<urunler> prd = new List<urunler>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p inner join categories as c on p.categoryID=c.categoryID  inner join resimler as r on p.productID=r.productID", sqlcon);
            
            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                urunler p = new urunler();
                p.productID = Convert.ToInt32(sdr["productID"]);
                p.productName = sdr["productname"].ToString();
                p.model = sdr["model"].ToString();
                p.price = Convert.ToDouble(sdr["price"]);
                p.categoryname = sdr["categoryname"].ToString();
                p.supplierID = Convert.ToInt32(sdr["supplierID"]);
                p.brand = sdr["brand"].ToString();
                p.stock = Convert.ToUInt32(sdr["stock"]);
                p.unitsonorder = Convert.ToInt32(sdr["unitsonorder"]);
                p.reorderlevel = Convert.ToInt32(sdr["reorderlevel"]);
                p.discount = Convert.ToDouble(sdr["discount"]);
                p.status = Convert.ToInt32(sdr["status"]);
                p.adddate = sdr["adddate"].ToString();
                p.photopath = sdr["photopath"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }  // p.urunlergetircatIDli();  static olmadıgı icin , nesne yaratılarak cagrılır ..Değişen veri döndürdügü icin static yapmadık..birden fazla deger döndürür
    








        //LİNQ SORGUSU
        public List<urunler> urunlergetirmarkali()
        {
            List<urunler> ulist = new List<urunler>();

         var list = (from p in db.products
                     join r in db.resimlers on p.productID equals r.productID
                     where p.supplierID == supplierID
                     select new {p,r}).ToList();

         foreach (var item in list)
         {
             urunler u = new urunler();
             u.productID = item.p.productID;
             u.productName = item.p.productname;
             u.categoryID = (int)item.p.categoryID;
             u.photopath = item.r.photopath;
             u.price = (double)item.p.price;
             u.model = item.p.productname;
             u.stock = (int)item.p.stock;
             ulist.Add(u);
         }
            return ulist;
        }









        //LİNQ SORGUSU
        public List<urunler> urunlergetirenyeni()
        {
            List<urunler> ulist = new List<urunler>();

            var list = (from p in db.products
                        join r in db.resimlers on p.productID equals r.productID
                        orderby p.adddate descending
                        select new { p, r }).Take(6).ToList();

            foreach (var item in list)
            {
                urunler u = new urunler();
                u.productID = item.p.productID;
                u.productName = item.p.productname;
                u.categoryID = (int)item.p.categoryID;
                u.photopath = item.r.photopath;
                u.price = (double)item.p.price;
                u.model = item.p.productname;
                u.stock = (int)item.p.stock;
                ulist.Add(u);
            }
            return ulist;
        }



        public static List<ikiliurun> urunlergetirstring(string urunadi)
        {
            List<ikiliurun> prd = new List<ikiliurun>();
            SqlConnection sqlcon = connection.baglanti;
            SqlCommand sqlcmd = new SqlCommand("select * from products as p inner join categories as c on p.categoryID=c.categoryID  inner join resimler as r on p.productID=r.productID where p.productname like '"+urunadi+"%'",sqlcon);
           

            sqlcon.Open();
            SqlDataReader sdr = sqlcmd.ExecuteReader();
            while (sdr.Read())
            {
                ikiliurun p = new ikiliurun();
                p.urunadi = sdr["productname"].ToString();
                prd.Add(p);
            }
            sqlcon.Close();
            return prd;
        }  // p.urunlergetircatIDli();  static olmadıgı icin , nesne yaratılarak cagrılır ..Değişen veri döndürdügü icin static yapmadık..birden fazla deger döndürür

    }
}